﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PMS.ENT;
using PMS.Utilities;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace PMS.DAL
{
    public class SubjectDAL
    {
        #region Common
        private static SubjectENT CreateSubjectFromReader(IDataReader reader)
        {
            SubjectENT item = new SubjectENT();
            try
            {

                if (!reader.IsDBNull(reader.GetOrdinal(SubjectColumns.SubjectID.ToString()))) item.SubjectID = Utility.IntParse(reader[SubjectColumns.SubjectID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SubjectColumns.SubjectName.ToString()))) item.SubjectName = Utility.StringParse(reader[SubjectColumns.SubjectName.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SubjectColumns.SubjectCode.ToString()))) item.SubjectCode = Utility.StringParse(reader[SubjectColumns.SubjectCode.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SubjectColumns.CreatedBy.ToString()))) item.CreatedBy = Utility.IntParse(reader[SubjectColumns.CreatedBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SubjectColumns.UpdateBy.ToString()))) item.UpdateBy = Utility.IntParse(reader[SubjectColumns.UpdateBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SubjectColumns.CreatedDate.ToString()))) item.CreatedDate = Utility.DateTimeParse(reader[SubjectColumns.CreatedDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SubjectColumns.UpdateDate.ToString()))) item.UpdateDate = Utility.DateTimeParse(reader[SubjectColumns.UpdateDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SubjectColumns.Isdeleted.ToString()))) item.Isdeleted = Utility.IntParse(reader[SubjectColumns.Isdeleted.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SubjectColumns.DepartmentID.ToString()))) item.DepartmentID = Utility.IntParse(reader[SubjectColumns.DepartmentID.ToString()]);

                DataTable dt = reader.GetSchemaTable();
                if (Utility.FieldExist(dt, SubjectColumns.DepartmentName.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(SubjectColumns.DepartmentName.ToString()))) item.DepartmentName = Utility.StringParse(reader[SubjectColumns.DepartmentName.ToString()]);

                }
             
            }
            catch
            {
                throw;
            }
            return item;
        }
        #endregion

        #region Get
        public static SubjectENT GetSubjectByID(int subjectID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procSubjectGetByID.ToString());
                database.AddInParameter(dbCommand, "@SubjectID", DbType.Int32, subjectID);

                SubjectENT subject = new SubjectENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        subject = CreateSubjectFromReader(reader);
                    }
                    reader.Close();
                }

                return subject;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetSubjectByID");
            }
        }

        public static SubjectCollection GetSubjectList(string subjectname, string subjectcode, int departmentID, string orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procSubjectGetList.ToString());
                SubjectCollection listSubject = new SubjectCollection();

                database.AddInParameter(dbCommand, "@SubjectName", DbType.String, subjectname);
                database.AddInParameter(dbCommand, "@SubjectCode", DbType.String, subjectcode);
                database.AddInParameter(dbCommand, "@DepartmentID", DbType.String, departmentID);
                database.AddInParameter(dbCommand, "@OrderBy", DbType.String, orderBy);
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection);
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listSubject.Add(CreateSubjectFromReader(reader));
                    }
                    reader.Close();
                }

                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return listSubject;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetSubjectList");
            }
        }

        public static SubjectCollection GetSubjectListForCMB(int departmentID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procSubjectGetListForCMB.ToString());
                database.AddInParameter(dbCommand, "@DepartmentID", DbType.Int32, departmentID);

                SubjectCollection listSubject = new SubjectCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listSubject.Add(CreateSubjectFromReader(reader));
                    }
                    reader.Close();
                }

                return listSubject;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetSubjectListForCMB");
            }
        }
        #endregion

        #region Insert, Update, Delete

        public static int InsertSubject(SubjectENT Subject)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procSubjectInsert.ToString());
                int SubjectID = 0;
                database.AddOutParameter(dbCommand, "@SubjectID", DbType.Int32, Subject.SubjectID);
                database.AddInParameter(dbCommand, "@SubjectName", DbType.String, Subject.SubjectName);
                database.AddInParameter(dbCommand, "@SubjectCode", DbType.String, Subject.SubjectCode);
                database.AddInParameter(dbCommand, "@DepartmentID", DbType.Int32, Subject.DepartmentID);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, Subject.CreatedBy);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    SubjectID = Utility.IntParse(database.GetParameterValue(dbCommand, "@SubjectID"));
                    return SubjectID;
                }
                return -1;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertSubject");
            }
        }

        public static int UpdateSubject(SubjectENT Subject)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procSubjectUpdate.ToString());

                database.AddInParameter(dbCommand, "@SubjectID", DbType.Int32, Subject.SubjectID);
                database.AddInParameter(dbCommand, "@SubjectName", DbType.String, Subject.SubjectName);
                database.AddInParameter(dbCommand, "@SubjectCode", DbType.String, Subject.SubjectCode);
                database.AddInParameter(dbCommand, "@DepartmentID", DbType.Int32, Subject.DepartmentID);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, Subject.UpdateBy);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    int SubjectID = Utility.IntParse(database.GetParameterValue(dbCommand, "@SubjectID"));
                    return SubjectID;
                }
                return -1;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_UpdateSubject");
            }
        }

        public static int DeleteSubject(int SubjectID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procSubjectDelete.ToString());
                database.AddInParameter(dbCommand, "@SubjectID", DbType.Int32, SubjectID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_DeleteSubject");
            }
        }
        #endregion
    }
}
